using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Configuration;
using System.Xml;
using System.Xml.Serialization;

public partial class ExportData : System.Web.UI.Page
{
	private AdHocSql _oAdHocSql;
	private Regex _isGuid = new Regex(@"^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", RegexOptions.Compiled);
	private string _sql;
	
#region "Event Handlers"
    protected void Page_PreRender(object sender, System.EventArgs e)
	{
		if (!Page.IsPostBack)
		{
			try
			{
				// Load Xml data
				XmlReader xr = XmlReader.Create(Server.MapPath("App_Data\\AdHocSql.xml"));
				XmlDocument xd = new XmlDocument();
				xd.Load(xr);
				XmlNodeReader xnr = new XmlNodeReader(xd.DocumentElement);
				XmlSerializer xs = new XmlSerializer(typeof(AdHocSql));
				_oAdHocSql = (AdHocSql)xs.Deserialize(xnr);

				//Increase the script timeout since this could take a long time
				Server.ScriptTimeout = 360;

				AdHocSqlStatement sql = ValidateRequest();
				if (sql != null)
					GetResults(sql);
				else
					DisplayError("Invalid request");
			}
			catch (System.Threading.ThreadAbortException ex)
			{
				// Just ignore this error.
			}
			catch (Exception ex)
			{
				DisplayException(ex);
			}
        }

    }
#endregion

#region "Private Methods"
	private void DisplayError(string msg)
	{
		Response.Write("<span style=\"color:red;\">" + msg + "</span>");
	}

	private void DisplayException(Exception ex)
	{
		StringBuilder sb = new StringBuilder();
		Exception innerEx = ex;
		while (innerEx != null)
		{
			sb.AppendFormat("{0}<br/>", innerEx.Message);
			innerEx = innerEx.InnerException;
		}
		Response.Write("<span style=\"color:red;\">" + sb.ToString() + "</span>");
	}

	private bool GetResults(AdHocSqlStatement adHocSql)
	{
		string fldValue;
		string name = "";
		int orderby = 0;
		string sOrderBy = "";
		bool fSuccess = false;
		try
		{
			// Initialize the SQL String
			_sql = "";

			using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings[Request["conn"]].ConnectionString))
			{
				// If the statement a procedure
				bool isProc = false;
				if (adHocSql.Statement.ToLower().Contains("exec"))
					isProc = true;
				// Get the statement
				_sql = adHocSql.Statement;
				if (_sql.ToLower().Contains("{parameters}"))
				{
					orderby = _sql.ToLower().IndexOf("{parameters}");
					_sql = _sql.ToLower().Replace("{parameters}", "");
					if (_sql.Length > orderby)
					{
						sOrderBy = _sql.Substring(orderby);
						_sql = _sql.Substring(0, orderby - 1);
					}
				}
				if (!isProc && adHocSql.Parameters.Count > 0) _sql += " where ";
				// Loop through each parameter and append to sql statement
				for (int i = 0; i < adHocSql.Parameters.Count; i++)
				{
					name = "@" + RemovePrefix(adHocSql.Parameters[i].Name);

					if (!isProc)
					{
						if (i > 0)
							_sql += String.Format(" {0}", Request["Logic" + i.ToString()]);

						// Get the request value
						fldValue = Request[RemovePrefix(adHocSql.Parameters[i].Name)];
						// If we are processing the 'is' or 'is not' operator, and the value is 'null'
						if ((Request["operator" + i] == " is " || Request["operator" + i] == " is not ") && fldValue.Trim().ToLower() == "null")
						{
							// Change the syntax of the sql statement to accommodate is null or is not null
							_sql += string.Format(" {0} {1} {2}", adHocSql.Parameters[i].Name, Request["Operator" + i], fldValue);
						}
						else
						{
							// Else parameterize the sql statement field and value
							_sql += string.Format(" {0} {1} {2}", adHocSql.Parameters[i].Name, Request["Operator" + i], name);
						}
					}
					else
					{
						// Sql statement is a stored procedure
						if (i > 0)
							_sql += ",";

						_sql += string.Format(" {0}", name);
					}
				}
				if (!string.IsNullOrEmpty(sOrderBy))
				{
					_sql += " " + sOrderBy;
				}
				// Open database
				conn.Open();
				// Create command object with SQL query like before, but with parameter instead of hard coding
				SqlCommand comm = new SqlCommand(_sql, conn);
				// Loop through each parameter and add to sql command
				for (int i = 0; i < adHocSql.Parameters.Count; i++)
				{
					name = "@" + RemovePrefix(adHocSql.Parameters[i].Name);

					// Get the request value
					fldValue = Request[RemovePrefix(adHocSql.Parameters[i].Name)];
					// If we are not processing the 'is' and 'is not' operator, or the value is not 'null'
					if ((Request["operator" + i] != " is " && Request["operator" + i] != " is not ") || fldValue.Trim().ToLower() != "null")
					{
						// Create a parameter and convert the request value
						switch (adHocSql.Parameters[i].DataType.ToLower())
						{
							case "int":
								comm.Parameters.Add(name, SqlDbType.Int);
								comm.Parameters[name].Value = Convert.ToInt32(fldValue);
								break;
							case "string":
								comm.Parameters.Add(name, SqlDbType.NVarChar);
								comm.Parameters[name].Value = fldValue;
								break;
							case "double":
								comm.Parameters.Add(name, SqlDbType.Decimal);
								comm.Parameters[name].Value = Convert.ToDecimal(fldValue);
								break;
							case "date":
								comm.Parameters.Add(name, SqlDbType.DateTime);
								comm.Parameters[name].Value = Convert.ToDateTime(fldValue);
								break;
							case "uniqueid":
								comm.Parameters.Add(name, SqlDbType.NVarChar);
								comm.Parameters[name].Value = Convert.ToString(fldValue);
								break;
							default:
								break;
						}
					}
				}
				DataSet ds = new DataSet();
				SqlDataAdapter da = new SqlDataAdapter(comm);
				da.Fill(ds);
				// Use data on some way... 
				if (ds != null && ds.Tables.Count > 0)
				{
					if (ds.Tables[0].Rows.Count == 0)
					{
						DisplayError("No results returned");
					}
					else
					{
						// This is where I process the CSV output
						ProcessOutput(ds.Tables[0]);
						fSuccess = true;
					}
				}
				else
				{
					DisplayError("No results returned");
				}
				// Close connection
				conn.Close();
			}
		}
		catch (System.Threading.ThreadAbortException ex)
		{
			// Just ignore this error.
		}
		catch (Exception ex)
		{
			DisplayException(ex);
		}
		return fSuccess;
	}

	private void ProcessOutput(DataTable dt)
	{
		int iCol = 0;
		int iRow = 0;
		string strFile = "Export_";

		// Clear the decks
		Response.Clear();
		Response.ContentType = "text/csv";
		Response.Buffer = true;
		Response.AddHeader("Content-Disposition", String.Format("attachment; filename={0}_{1}.csv", strFile, DateTime.Now.ToString("yyMMdd_HHmmss")));
		// Write the Header
		foreach (DataColumn dc in dt.Columns)
		{
			if (iCol > 0) Response.Write(",");
			Response.Write(dc.ColumnName);
			iCol++;
		}
		Response.Write(Environment.NewLine);
		// Write the Data
		foreach (DataRow dr in dt.Rows)
		{
			iCol = 0;
			for (int i = 0; i <= dt.Columns.Count - 1; i++)
			{
				if (i > 0) Response.Write(",");
				if (dr[dt.Columns[i].ColumnName].ToString().Contains(","))
					Response.Write(String.Format("\"{0}\"", dr[dt.Columns[i].ColumnName]));
				else
					Response.Write(dr[dt.Columns[i].ColumnName]);
			}
			Response.Write(Environment.NewLine);
			iRow++;
		}
		// Done
		Response.End();
	}

	private string RemovePrefix(string name)
	{
		string newName = name;
		int offset = name.IndexOf(".");
		if (offset >= 0)
			newName = name.Substring(offset + 1);

		return newName;
	}

	private AdHocSqlStatement ValidateRequest()
	{
		AdHocSqlStatement sql = null;
		string title = "";
		try
		{
			if (!String.IsNullOrEmpty(Request["conn"]) && !String.IsNullOrEmpty(Request["sql"]))
			{
				title = Request["sql"].ToLower();
				foreach (AdHocSqlStatement adHocSql in _oAdHocSql.SqlStatements)
				{
					if (adHocSql.Title.ToLower() == title)
					{
						sql = adHocSql;
						break;
					}
				}
			}
		}
		catch (Exception ex)
		{
			DisplayException(ex);
		}
		return sql;
	}
#endregion
}
